using MongoDB.Bson;
using MongoDB.Driver;
using Sustainalytics.AwsS3.Client;
using Sustainalytics.Entities;
using Sustainalytics.Entities.RiskRatings;
using Sustainalytics.MongoDB.Extensions;
using Sustainalytics.Utils.Configuration;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace Sustainalytics.RiskRatings.Reporting
{
    public class ReportDataProvider
    {
        private readonly CancellationToken _token;
        private readonly int _bulkSize;
        private int _skip;
        private readonly string _dwConnectionString;
        private readonly string _inputFileconnectionString;

        public ReportDataProvider(int bulkSize, CancellationToken token, string dwConnectionString, string inputFileconnectionString)
        {
            _token = token;
            _dwConnectionString = dwConnectionString;
            _inputFileconnectionString = inputFileconnectionString;
            _bulkSize = bulkSize;
            _skip = 0;
        }

        public ReportDataSource[] GetCompanyReportData()
        {
            return GetCompanyReportData(Guid.Empty, false);
        }

        public ReportDataSource[] GetCompanyReportData_Core()
        {
            return GetCompanyReportData(Guid.Empty, false, true);
        }

        public ReportDataSource[] GetCompanyReportData(Guid companyId, bool isSummaryReport)
        {
            return GetCompanyReportData(companyId, isSummaryReport, false);
        }

        public ReportDataSource[] GetCompanyReportData(Guid companyId, bool isSummaryReport, bool isCoreCompany)
        {
            return GetNextData(companyId, isSummaryReport, isCoreCompany);
        }

        private ReportDataSource[] GetNextData(Guid companyId, bool isSummaryReport, bool isCoreCompany)
        {
            var batch = new ConcurrentBag<ReportDataSource>();
            List<RiskRatingResearchProfile> companyProfiles = new List<RiskRatingResearchProfile>();
            List<CoreRiskRatingResearchProfile> companyCoreProfiles = new List<CoreRiskRatingResearchProfile>();

            if (companyId != Guid.Empty)
            {
                if (isCoreCompany)
                {
                    companyCoreProfiles = MongoFactory.GetCollectionFromDatabase<CoreRiskRatingResearchProfile>(_dwConnectionString)
                     .Find(x => x.Id == companyId)
                     .ToList();
                }
                else
                {
                    companyProfiles = MongoFactory.GetCollectionFromDatabase<RiskRatingResearchProfile>(_dwConnectionString)
                        .Find(x => x.Id == companyId)
                        .ToList();
                }
            }
            else
            {
                if (isCoreCompany)
                {
                    companyCoreProfiles = MongoFactory.GetCollectionFromDatabase<CoreRiskRatingResearchProfile>(_dwConnectionString)
                    .Find(FilterDefinition<CoreRiskRatingResearchProfile>.Empty)
                    .Skip(_skip)
                    .Limit(_bulkSize)
                    .ToList();
                }
                else
                {
                    companyProfiles = MongoFactory.GetCollectionFromDatabase<RiskRatingResearchProfile>(_dwConnectionString)
                    .Find(FilterDefinition<RiskRatingResearchProfile>.Empty)
                    .Skip(_skip)
                    .Limit(_bulkSize)
                    .ToList();
                }
            }

            _skip += _bulkSize;
            if (!companyProfiles.Any() && !companyCoreProfiles.Any())
                return batch.ToArray();

            var maxDegreeOfParallelism = SettingsHelper.Read("MaxDegreeOfParallelism", 1);
            
            var options = new ParallelOptions()
            {
                CancellationToken = _token,
                MaxDegreeOfParallelism = maxDegreeOfParallelism
            };

            Dictionary<Guid, RiskRatingBenchmark> benchmarkDictionary = new Dictionary<Guid, RiskRatingBenchmark>();
            Dictionary<string, List<RiskRatingCluster>> riskRatingClusterDictionary = new Dictionary<string, List<RiskRatingCluster>>();
            Dictionary<Guid, List<MaterialIssuePerformance>> materialIssuesOverviewDictionary = new Dictionary<Guid, List<MaterialIssuePerformance>>();
            Dictionary<string, IndustryAggregate> industryAggregateDictionary = new Dictionary<string, IndustryAggregate>();
            Dictionary<int, SubIndustryAggregate> subIndustryPeerAggregateDictionary = new Dictionary<int, SubIndustryAggregate>();

            Dictionary<int, UniverseMaterialEsgIssueAggregate> universeMEIPeerAggregateDictionary = new Dictionary<int, UniverseMaterialEsgIssueAggregate>();
            Dictionary<string, Dictionary<int, IndustryMaterialEsgIssueAggregate>> industryMEIPeerAggregateDictionary = new Dictionary<string, Dictionary<int, IndustryMaterialEsgIssueAggregate>>();
            Dictionary<int, Dictionary<int, SubIndustryMaterialEsgIssueAggregate>> subIndustryMEIPeerAggregateDictionary = new Dictionary<int, Dictionary<int, SubIndustryMaterialEsgIssueAggregate>>();
            UniverseAggregate universeAggregate = new UniverseAggregate();
            Dictionary<int, List<SubIndustryRiskRatingPerformance>> subIndustryRiskRatingPerformanceDictionary = new Dictionary<int, List<SubIndustryRiskRatingPerformance>>();
            List<GlobalUniverseRiskRatingPerformance> globalUniverseRiskRatingPerformanceList = new  List<GlobalUniverseRiskRatingPerformance>();
            List<ExposureCluster > exposureClusters = new List<ExposureCluster> ();
            List<ManagementCluster> managementClusters = new List<ManagementCluster>();
            List<MeiDescription> meiDescriptions= new List<MeiDescription>();
            Dictionary<int, Dictionary<int, List<SubIndustryRiskRatingPerformance>>> subIndustryMEIRiskRatingPerformanceDictionary = new Dictionary<int, Dictionary<int, List<SubIndustryRiskRatingPerformance>>>();
            Dictionary<int, List<GlobalUniverseRiskRatingPerformance>> globalUniverseMEIRiskRatingPerformanceDictionary = new  Dictionary<int, List<GlobalUniverseRiskRatingPerformance>>();
            Dictionary<Guid, List<GovernancePillars>> governancePillarsDictionary = new Dictionary<Guid, List<GovernancePillars>>();

            byte[] glossary = new byte[0];
            IDictionary<Guid, IEnumerable<EventIndicator>> eventsDictionary = null;
            IDictionary<Guid, IEnumerable<Incident>> incidentDictionary = null;
            var exceptions = new ConcurrentQueue<Exception>();

            if (!isCoreCompany)
            {
                var actions = new List<Action>
                {
                    () => { riskRatingClusterDictionary = GetRiskRatingClusters(); },
                    () => { benchmarkDictionary = GetBenchmark(companyProfiles); },
                    () => { materialIssuesOverviewDictionary = GetMaterialIssuesOverview(companyProfiles); },

                    () => { universeAggregate = GetUniversePeerAggregate(); },
                    () => { industryAggregateDictionary = GetIndustryAggregateList(companyProfiles); },
                    () => { subIndustryPeerAggregateDictionary = GetSubIndustryPeerAggregateList(companyProfiles); },

                () => { universeMEIPeerAggregateDictionary = GetUniverseMEIPeerAggregateList(companyProfiles); },
                () => { industryMEIPeerAggregateDictionary = GetIndustryMEIPeerAggregateList(companyProfiles); },
                    () => { subIndustryMEIPeerAggregateDictionary = GetSubIndustryMEIPeerAggregateList(companyProfiles); },

                    () => { subIndustryRiskRatingPerformanceDictionary = GetSubIndustryRiskRatingPerformanceDictionary(companyProfiles); },
                () => { globalUniverseRiskRatingPerformanceList = GetGlobalUniverseRiskRatingPerformanceList(); },
                () => { exposureClusters = GetExposureClusters(); },
                () => { managementClusters = GetManagementClusters(); },
                () => { meiDescriptions = GetMEIsDescriptions(); },
                    () => { subIndustryMEIRiskRatingPerformanceDictionary = GetSubIndustryMEIRiskRatingPerformanceDictionary(companyProfiles); },
                    () => { globalUniverseMEIRiskRatingPerformanceDictionary = GetGlobalUniverseMEIRiskRatingPerformanceDictionary(); },
                    () => { glossary = GetGlossaryPdf(isSummaryReport, isCoreCompany); },
                ()=>  { eventsDictionary= GetEventsAsync(companyProfiles).Result; },
                ()=>  { incidentDictionary= GetIncidentsAsync(companyProfiles).Result; },
                ()=>  { governancePillarsDictionary= GetGovernancePillarsDictionary(companyProfiles); },
                };
                Parallel.ForEach(actions, options, action =>
                {
                    try
                    {
                        action();
                    }
                    catch (Exception ex)
                    {
                        exceptions.Enqueue(ex);
                    }
                });
                if (exceptions.Count > 0)
                {
                    throw new AggregateException(exceptions);
                }
                exceptions = new ConcurrentQueue<Exception>();
                Parallel.ForEach(companyProfiles, options, companyProfile =>
                {
                    try
                    {
                        var reportDataSource = new ReportDataSource
                        {
                            CompanyId = companyProfile.Id,

                            ReportType = isSummaryReport ? ReportType.SummaryRiskRatingsReport : ReportType.RiskRatingsReport,

                            CompanyProfile = companyProfile,

                            CoreCompanyProfile = new CoreRiskRatingResearchProfile
                            {
                                Id = companyProfile.Id,
                                SubPeerGroup = companyProfile.SubPeerGroup,
                                PeerGroup = companyProfile.PeerGroup,
                                CompanyName = companyProfile.CompanyName,
                                CompanyId = companyProfile.CompanyId,
                                CompanyProfileVersion = companyProfile.CompanyProfileVersion,
                                ManagementRisk = companyProfile.ManagementRisk,
                                LastUpdate = companyProfile.LastUpdate,
                                Cluster = companyProfile.Cluster,
                                Assessment = companyProfile.Assessment,
                                ExposureCluster = companyProfile.ExposureCluster,
                                ManagementCluster = companyProfile.ManagementCluster,
                                ManageableCluster = companyProfile.ManageableCluster,
                            },

                            Glossary = glossary,

                            Benchmark = benchmarkDictionary.ContainsKey(companyProfile.Id)
                                ? benchmarkDictionary[companyProfile.Id]
                                : new RiskRatingBenchmark(),

                            OverallClusters = riskRatingClusterDictionary.ContainsKey(RiskRatingClusterType.Overall.ToString())
                                ? riskRatingClusterDictionary[RiskRatingClusterType.Overall.ToString()]
                                : new List<RiskRatingCluster>(),

                            MaterialIssuesClusters = riskRatingClusterDictionary.ContainsKey(RiskRatingClusterType.MEI.ToString())
                                ? riskRatingClusterDictionary[RiskRatingClusterType.MEI.ToString()]
                                : new List<RiskRatingCluster>(),

                            MaterialIssuesOverview = materialIssuesOverviewDictionary.ContainsKey(companyProfile.Id)
                                ? materialIssuesOverviewDictionary[companyProfile.Id]
                                : new List<MaterialIssuePerformance>(),

                            UniverseAggregateDataPoints = universeAggregate,
                            IndustryAggregateDataPoints = industryAggregateDictionary.ContainsKey(companyProfile.PeerGroup)
                                ? industryAggregateDictionary[companyProfile.PeerGroup]
                                : new IndustryAggregate(),
                            SubIndustryAggregateDataPoints = subIndustryPeerAggregateDictionary.ContainsKey(companyProfile.SubPeerGroupId)
                                ? subIndustryPeerAggregateDictionary[companyProfile.SubPeerGroupId]
                                : new SubIndustryAggregate(),

                            UniverseMEIAggregateDataPointsDictionary = universeMEIPeerAggregateDictionary,
                            ExposureClusters = exposureClusters,
                            ManagementClusters = managementClusters,
                            MEIDescriptions=meiDescriptions,
                            IndustryMEIAggregateDataPointsDictionary = industryMEIPeerAggregateDictionary.ContainsKey(companyProfile.PeerGroup)
                            ? industryMEIPeerAggregateDictionary[companyProfile.PeerGroup]
                            : new Dictionary<int, IndustryMaterialEsgIssueAggregate>(),
                            SubIndustryMEIAggregateDataPointsDictionary = subIndustryMEIPeerAggregateDictionary.ContainsKey(companyProfile.SubPeerGroupId)
                                ? subIndustryMEIPeerAggregateDictionary[companyProfile.SubPeerGroupId]
                                : new Dictionary<int, SubIndustryMaterialEsgIssueAggregate>(),

                            SubIndustryRiskRatingPerformanceList = subIndustryRiskRatingPerformanceDictionary.ContainsKey(companyProfile.SubPeerGroupId)
                                ? subIndustryRiskRatingPerformanceDictionary[companyProfile.SubPeerGroupId]
                                : new List<SubIndustryRiskRatingPerformance>(),

                            GlobalUniverseRiskRatingPerformanceList = globalUniverseRiskRatingPerformanceList,

                            SubIndustryMEIRiskRatingPerformanceDictionary = subIndustryMEIRiskRatingPerformanceDictionary.ContainsKey(companyProfile.SubPeerGroupId)
                                ? subIndustryMEIRiskRatingPerformanceDictionary[companyProfile.SubPeerGroupId]
                                : new Dictionary<int, List<SubIndustryRiskRatingPerformance>>(),

                            GlobalUniverseMEIRiskRatingPerformanceDictionary = globalUniverseMEIRiskRatingPerformanceDictionary,

                            Events = eventsDictionary.ContainsKey(companyProfile.Id) ? eventsDictionary[companyProfile.Id] : Enumerable.Empty<EventIndicator>(),

                            Incident = incidentDictionary.ContainsKey(companyProfile.Id) ? incidentDictionary[companyProfile.Id] : Enumerable.Empty<Incident>(),

                            GovernancePillarsList = governancePillarsDictionary.ContainsKey(companyProfile.Id)
                                ? governancePillarsDictionary[companyProfile.Id]
                                : new List<GovernancePillars>(),
                        };
                        batch.Add(reportDataSource);
                    }
                    catch (Exception ex)
                    {
                        exceptions.Enqueue(ex);
                    }
                });
            }
            else
            {
                var actions = new List<Action>
                {
                    () => { riskRatingClusterDictionary = GetRiskRatingClusters(); },
                    () => { benchmarkDictionary = GetBenchmark(companyCoreProfiles); },
                    () => { universeAggregate = GetUniversePeerAggregate(); },
                    () => { industryAggregateDictionary = GetIndustryAggregateList(companyCoreProfiles); },
                    () => { subIndustryPeerAggregateDictionary = GetSubIndustryPeerAggregateList(companyCoreProfiles); },
                    () => { subIndustryRiskRatingPerformanceDictionary = GetSubIndustryRiskRatingPerformanceDictionary(companyCoreProfiles); },
                    () => { globalUniverseRiskRatingPerformanceList = GetGlobalUniverseRiskRatingPerformanceList(); },
                    () => { exposureClusters = GetExposureClusters(); },
                    () => { managementClusters = GetManagementClusters(); },
                    () => { glossary = GetGlossaryPdf(isSummaryReport, isCoreCompany); },
                    ()=>  { eventsDictionary= GetEventsAsync(companyCoreProfiles).Result; },
                    ()=>  { incidentDictionary= GetIncidentsAsync(companyCoreProfiles).Result; },
                };

                Parallel.ForEach(actions, options, action =>
                {
                    try
                    {
                        action();
                    }
                    catch (Exception ex)
                    {
                        exceptions.Enqueue(ex);
                    }
                });

                if (exceptions.Count > 0)
                {
                    throw new AggregateException(exceptions);
                }

                exceptions = new ConcurrentQueue<Exception>();

                Parallel.ForEach(companyCoreProfiles, options, companyCoreProfile =>
                {
                    try
                    {
                        var reportDataSource = new ReportDataSource
                        {
                            CompanyId = companyCoreProfile.Id,

                            ReportType = isSummaryReport ? ReportType.SummaryCoreRiskRatingsReport : ReportType.CoreRiskRatingsReport,

                            CompanyProfile = new RiskRatingResearchProfile
                            {
                                Id = companyCoreProfile.Id,
                                SubPeerGroup = companyCoreProfile.SubPeerGroup,
                                PeerGroup = companyCoreProfile.PeerGroup,
                                CompanyName = companyCoreProfile.CompanyName,
                                CompanyId = companyCoreProfile.CompanyId,
                                CompanyProfileVersion = companyCoreProfile.CompanyProfileVersion,
                                ManagementRisk = companyCoreProfile.ManagementRisk,
                                LastUpdate = companyCoreProfile.LastUpdate,
                                Cluster = companyCoreProfile.Cluster,
                                Assessment = companyCoreProfile.Assessment,
                                ExposureCluster = companyCoreProfile.ExposureCluster,
                                ManagementCluster = companyCoreProfile.ManagementCluster,
                                ManageableCluster = companyCoreProfile.ManageableCluster,
                                SubIndustryExposureScore = companyCoreProfile.SubIndustryExposureScore,
                            },

                            CoreCompanyProfile = companyCoreProfile,

                            Glossary = glossary,

                            Benchmark = benchmarkDictionary.ContainsKey(companyCoreProfile.Id)
                                ? benchmarkDictionary[companyCoreProfile.Id]
                                : new RiskRatingBenchmark(),

                            OverallClusters = riskRatingClusterDictionary.ContainsKey(RiskRatingClusterType.Overall.ToString())
                                ? riskRatingClusterDictionary[RiskRatingClusterType.Overall.ToString()]
                                : new List<RiskRatingCluster>(),

                            MaterialIssuesClusters = riskRatingClusterDictionary.ContainsKey(RiskRatingClusterType.MEI.ToString())
                                ? riskRatingClusterDictionary[RiskRatingClusterType.MEI.ToString()]
                                : new List<RiskRatingCluster>(),

                            UniverseAggregateDataPoints = universeAggregate,

                            IndustryAggregateDataPoints = industryAggregateDictionary.ContainsKey(companyCoreProfile.PeerGroup)
                                ? industryAggregateDictionary[companyCoreProfile.PeerGroup]
                                : new IndustryAggregate(),

                            SubIndustryAggregateDataPoints = subIndustryPeerAggregateDictionary.ContainsKey(companyCoreProfile.SubPeerGroupId)
                                ? subIndustryPeerAggregateDictionary[companyCoreProfile.SubPeerGroupId]
                                : new SubIndustryAggregate(),

                            SubIndustryRiskRatingPerformanceList = subIndustryRiskRatingPerformanceDictionary.ContainsKey(companyCoreProfile.SubPeerGroupId)
                                ? subIndustryRiskRatingPerformanceDictionary[companyCoreProfile.SubPeerGroupId]
                                : new List<SubIndustryRiskRatingPerformance>(),

                            GlobalUniverseRiskRatingPerformanceList = globalUniverseRiskRatingPerformanceList,

                            ExposureClusters = exposureClusters,

                            ManagementClusters = managementClusters,

                            Events = eventsDictionary.ContainsKey(companyCoreProfile.Id) ? eventsDictionary[companyCoreProfile.Id] : Enumerable.Empty<EventIndicator>(),

                            Incident = incidentDictionary.ContainsKey(companyCoreProfile.Id) ? incidentDictionary[companyCoreProfile.Id] : Enumerable.Empty<Incident>(),
                        };
                        batch.Add(reportDataSource);
                    }
                    catch (Exception ex)
                    {
                        exceptions.Enqueue(ex);
                    }
                });
            }

            if (exceptions.Count > 0)
            {
                throw new AggregateException(exceptions);
            }
            return batch.ToArray();
        }

        private Dictionary<Guid, List<GovernancePillars>> GetGovernancePillarsDictionary(List<RiskRatingResearchProfile> companyProfiles)
        {
            var profileIds = companyProfiles.Select(cp => cp.Id).Distinct().ToList();
            var list = MongoFactory.GetCollectionFromDatabase<GovernancePillars>(_dwConnectionString)
                .Find(x => profileIds.Contains(x.CompanyId))
                .ToList();

            Dictionary<Guid, List<GovernancePillars>> dictionary = new Dictionary<Guid, List<GovernancePillars>>();
            if (list.Any())
            {
                dictionary = list.GroupBy(x => x.CompanyId).ToDictionary(x => x.Key, x => x.Select(w => w).ToList());
            }
            return dictionary;
        }

        private async Task<Dictionary<Guid, IEnumerable<EventIndicator>>> GetEventsAsync(IEnumerable<RiskRatingResearchProfileBase> companyProfiles)
        {
            var companyIds = companyProfiles.Select(c => c.Id).ToList();
            var list = await MongoFactory.GetCollectionFromDatabase<EventIndicator>(_dwConnectionString).Find(x => companyIds.Contains(x.CompanyId)).ToListAsync();
            return list.GroupBy(x => x.CompanyId).ToDictionary(x => x.Key, x => x.Select(w => w));
        }

        private async Task<Dictionary<Guid, IEnumerable<Incident>>> GetIncidentsAsync(IEnumerable<RiskRatingResearchProfileBase> companyProfiles)
        {
            var companyIds = companyProfiles.Select(c => c.Id).ToList();
            var list = await MongoFactory.GetCollectionFromDatabase<Incident>(_dwConnectionString).Find(x => companyIds.Contains(x.CompanyId)).ToListAsync();
            return list.GroupBy(x => x.CompanyId).ToDictionary(x => x.Key, x => x.Select(w => w));
        }

        private Dictionary<int, Dictionary<int, List<SubIndustryRiskRatingPerformance>>> GetSubIndustryMEIRiskRatingPerformanceDictionary(List<RiskRatingResearchProfile> companyProfiles)
        {
            var subPeerGroupIds = companyProfiles.Select(cp => cp.SubPeerGroupId).Distinct().ToList();
            var allMEI = MongoFactory.GetCollectionFromDatabase<RiskRatingMaterialEsgIssue>(_dwConnectionString)
                .Find(x => subPeerGroupIds.Contains(x.SubPeerGroupId))
                .Project(Builders< RiskRatingMaterialEsgIssue>.Projection.Expression (v => new SubIndustryRiskRatingPerformance
                {
                    CompanyId = v.Id,
                    SubPeerGroupId = v.SubPeerGroupId,
                    MEIId=v.ExternalId,
                    UnmanagedRisk = v.ManagementRisk.UnmanagedRisk.Value,
                    ManagementRisk = v.ManagementRisk.Management.Value,
                    Exposure = v.ManagementRisk.Exposure.Value,
                    Cluster = v.Cluster.Assessment
                }))
                .ToList();

            var dictionary = allMEI.GroupBy(x => x.SubPeerGroupId)
                                .ToDictionary(y => y.Key, y => y.GroupBy(z => z.MEIId).ToDictionary(w => w.Key,w => w.Select(v => v).ToList()));
            return dictionary;
        }

        private Dictionary<int, List<GlobalUniverseRiskRatingPerformance>> GetGlobalUniverseMEIRiskRatingPerformanceDictionary()
        {
            var allMEI = MongoFactory.GetCollectionFromDatabase<RiskRatingMaterialEsgIssue>(_dwConnectionString)
                .Find(new BsonDocument())
                .Project(Builders<RiskRatingMaterialEsgIssue>.Projection.Expression(v => new GlobalUniverseRiskRatingPerformance
                {
                    CompanyId = v.Id,
                    MEIId = v.ExternalId,
                    ManagementRisk = v.ManagementRisk.Management.Value,
                    Exposure = v.ManagementRisk.Exposure.Value,
                    Cluster = v.Cluster.Assessment
                }))
                .ToList();

            var dictionary = allMEI.GroupBy(x => x.MEIId)
                                .ToDictionary(w => w.Key, w => w.Select(v => v).ToList());
            return dictionary;
        }



        private Dictionary<int, List<SubIndustryRiskRatingPerformance>> GetSubIndustryRiskRatingPerformanceDictionary(IEnumerable<RiskRatingResearchProfileBase> companyProfiles)
        {
            var subPeerGroupIds = companyProfiles.Select(cp => cp.SubPeerGroupId).Distinct().ToList();
            List<SubIndustryRiskRatingPerformance> allProfiles = MongoFactory.GetCollectionFromDatabase<CoreRiskRatingResearchProfile>(_dwConnectionString)
                    .Find(x => subPeerGroupIds.Contains(x.SubPeerGroupId))
                    .Project(Builders<CoreRiskRatingResearchProfile>.Projection.Expression(v => new SubIndustryRiskRatingPerformance
                    {
                        CompanyId = v.Id,
                        SubPeerGroupId = v.SubPeerGroupId,
                        UnmanagedRisk = v.ManagementRisk.UnmanagedRisk.Value,
                        ManagementRisk = v.ManagementRisk.Management.Value,
                        Exposure = v.ManagementRisk.Exposure.Value,
                        Cluster = v.Cluster.Assessment
                    }))
                    .ToList();

            List<SubIndustryRiskRatingPerformance> allProfilesCore = MongoFactory.GetCollectionFromDatabase<RiskRatingResearchProfile>(_dwConnectionString)
                    .Find(x => subPeerGroupIds.Contains(x.SubPeerGroupId))
                    .Project(Builders<RiskRatingResearchProfile>.Projection.Expression(v => new SubIndustryRiskRatingPerformance
                    {
                        CompanyId = v.Id,
                        SubPeerGroupId = v.SubPeerGroupId,
                        UnmanagedRisk = v.ManagementRisk.UnmanagedRisk.Value,
                        ManagementRisk = v.ManagementRisk.Management.Value,
                        Exposure = v.ManagementRisk.Exposure.Value,
                        Cluster = v.Cluster.Assessment
                    }))
                    .ToList();

            var performance = allProfiles.Union(allProfilesCore).GroupBy(cp => cp.SubPeerGroupId)
                            .ToDictionary(y => y.Key, y => y.Select(v => v).ToList());

            return performance;
        }

        private  List<GlobalUniverseRiskRatingPerformance> GetGlobalUniverseRiskRatingPerformanceList()
        {
            List<GlobalUniverseRiskRatingPerformance> allUniverse = MongoFactory.GetCollectionFromDatabase<CoreRiskRatingResearchProfile>(_dwConnectionString)
                .Find(new BsonDocument())
                .Project(Builders<CoreRiskRatingResearchProfile>.Projection.Expression(v => new GlobalUniverseRiskRatingPerformance
                {
                    CompanyId = v.Id,
                    ManagementRisk = v.ManagementRisk.Management.Value,
                    Exposure = v.ManagementRisk.Exposure.Value,
                    Cluster = v.Cluster.Assessment
                }))
                .ToList();

            List<GlobalUniverseRiskRatingPerformance> allUniverseCore = MongoFactory.GetCollectionFromDatabase<RiskRatingResearchProfile>(_dwConnectionString)
                    .Find(new BsonDocument())
                    .Project(Builders<RiskRatingResearchProfile>.Projection.Expression(v => new GlobalUniverseRiskRatingPerformance
                    {
                        CompanyId = v.Id,
                        ManagementRisk = v.ManagementRisk.Management.Value,
                        Exposure = v.ManagementRisk.Exposure.Value,
                        Cluster = v.Cluster.Assessment
                    }))
                    .ToList();
            return allUniverse.Union(allUniverseCore).ToList();
        }

        private UniverseAggregate GetUniversePeerAggregate()
        {
            var universePeerAggregate = MongoFactory.GetCollectionFromDatabase<PeerAggregate>(_dwConnectionString)
                .OfType<UniverseAggregate>()
                .Find(new BsonDocument())
                .FirstOrDefault();
            return universePeerAggregate;
        }

        private Dictionary<string, IndustryAggregate> GetIndustryAggregateList(IEnumerable<RiskRatingResearchProfileBase> companyProfiles)
        {
            var industries = companyProfiles.Select(cp => cp.PeerGroup).Distinct().ToList();

            var industryAggregateList = MongoFactory.GetCollectionFromDatabase<PeerAggregate>(_dwConnectionString)
                .OfType<IndustryAggregate>()
                .Find(x => industries.Contains(x.Industry))
                .ToList()
                .Where(x => x.GetType() == typeof(IndustryAggregate));

            return industryAggregateList.ToDictionary(kv => kv.Industry);
        }

        private Dictionary<int, SubIndustryAggregate> GetSubIndustryPeerAggregateList(IEnumerable<RiskRatingResearchProfileBase> companyProfiles)
        {
            var subIndustryIds = companyProfiles.Select(cp => cp.SubPeerGroupId).Distinct().ToList();

            var subIndustryPeerAggregateList = MongoFactory.GetCollectionFromDatabase<PeerAggregate>(_dwConnectionString)
                .OfType<SubIndustryAggregate>()
                .Find(x => subIndustryIds.Contains(x.SubIndustryId))
                .ToList()
                .Where(x => x.GetType() == typeof(SubIndustryAggregate));

            return subIndustryPeerAggregateList.ToDictionary(kv => kv.SubIndustryId);
        }

        private Dictionary<int, UniverseMaterialEsgIssueAggregate> GetUniverseMEIPeerAggregateList(IEnumerable<RiskRatingResearchProfile> companyProfiles)
        {
            var meiExternalIds = from profile in companyProfiles
                                 from issue in profile.MaterialEsgIssues
                                 select issue.ExternalId;

            var industryMEIPeerAggregateList = MongoFactory.GetCollectionFromDatabase<PeerAggregate>(_dwConnectionString)
                .OfType<UniverseMaterialEsgIssueAggregate>()
                .Find(x => meiExternalIds.Contains(x.MEIExternalId))
                .ToList();

            var dictionary = industryMEIPeerAggregateList.ToDictionary(z => z.MEIExternalId);

            return dictionary;
        }

        private Dictionary<string, Dictionary<int, IndustryMaterialEsgIssueAggregate>> GetIndustryMEIPeerAggregateList(IEnumerable<RiskRatingResearchProfile> companyProfiles)
        {
            var industries = companyProfiles.Select(cp => cp.PeerGroup).Distinct().ToList();

            var meiExternalIds = from profile in companyProfiles
                                 from issue in profile.MaterialEsgIssues
                                 select issue.ExternalId;

            var industryMEIPeerAggregateList = MongoFactory.GetCollectionFromDatabase<PeerAggregate>(_dwConnectionString)
                .OfType<IndustryMaterialEsgIssueAggregate>()
                .Find(x => industries.Contains(x.Industry) && meiExternalIds.Contains(x.MEIExternalId))
                .ToList();

            var dictionary = industryMEIPeerAggregateList
                .GroupBy(x => x.Industry)
                .ToDictionary(kv => kv.Key, kv => kv.ToDictionary(z => z.MEIExternalId));

            return dictionary;
        }

        private Dictionary<int, Dictionary<int, SubIndustryMaterialEsgIssueAggregate>> GetSubIndustryMEIPeerAggregateList(IEnumerable<RiskRatingResearchProfile> companyProfiles)
        {
            var subIndustryIds = companyProfiles.Select(cp => cp.SubPeerGroupId).Distinct().ToList();
            var meiExternalIds = from profile in companyProfiles
                                 from issue in profile.MaterialEsgIssues
                                 select issue.ExternalId;
            var subIndustryMEIPeerAggregateList = MongoFactory.GetCollectionFromDatabase<PeerAggregate>(_dwConnectionString)
                .OfType<SubIndustryMaterialEsgIssueAggregate>()
                .Find(x => subIndustryIds.Contains(x.SubIndustryId) && meiExternalIds.Contains(x.MEIExternalId))
                .ToList();
            var dictionary = subIndustryMEIPeerAggregateList.GroupBy(x => x.SubIndustryId).ToDictionary(kv => kv.Key, kv => kv.ToDictionary( z => z.MEIExternalId));
            return dictionary;
        }

        private Dictionary<Guid, RiskRatingBenchmark> GetBenchmark(IEnumerable<RiskRatingResearchProfileBase> companyProfiles)
        {
            var profileIds = companyProfiles.Select(cp => cp.Id).Distinct().ToList();
            var benchmark = MongoFactory.GetCollectionFromDatabase<RiskRatingBenchmark>(_dwConnectionString)
                .Find(x => profileIds.Contains(x.Id))
                .ToList();
            var dictionary = benchmark.ToDictionary(kv => kv.Id);
            return dictionary;
        }
        private List<ExposureCluster> GetExposureClusters()
        {
            var exposureClusters = MongoFactory.GetCollectionFromDatabase<ExposureCluster>(_dwConnectionString)
           .Find(new BsonDocument())
           .ToList();
            return exposureClusters.OrderBy(x => x.LowerBound).ToList();
        }

        private List<MeiDescription> GetMEIsDescriptions()
        {
            var meiDescriptions = MongoFactory.GetCollectionFromDatabase<MeiDescription>(_dwConnectionString)
           .Find(new BsonDocument())
           .ToList();
            return meiDescriptions;
        }
        private List<ManagementCluster> GetManagementClusters()
        {
            var managementClusters = MongoFactory.GetCollectionFromDatabase<ManagementCluster>(_dwConnectionString)
           .Find(new BsonDocument())
           .ToList();
            return managementClusters.OrderBy(x => x.LowerBound).ToList();
        }
        private Dictionary<string, List<RiskRatingCluster>> GetRiskRatingClusters()
        {
            var clusters = MongoFactory.GetCollectionFromDatabase<RiskRatingCluster>(_dwConnectionString)
            .Find(new BsonDocument())
            .ToList();
            var dictionary = clusters.OrderBy(x=>x.LowerBound).GroupBy(c => c.Type).ToDictionary(kv => kv.Key.ToString(), kv => kv.ToList());
            return dictionary;
        }

        private Dictionary<Guid, List<MaterialIssuePerformance>> GetMaterialIssuesOverview(IEnumerable<RiskRatingResearchProfile> companyProfiles)
        {
            var subPeerGroupIds = companyProfiles.Select(cp => cp.SubPeerGroupId).Distinct().ToList();

            var averages = MongoFactory.GetCollectionFromDatabase<RiskRatingMeiSubPeerGroupAverage>(_dwConnectionString)
                .Find(x => subPeerGroupIds.Contains(x.SubPeerGroupId))
                .ToList()
                .ToDictionary(x => Tuple.Create<int, int>(x.SubPeerGroupId, x.ExternalId), x => x);

            var materialIssues = new ConcurrentBag<MaterialIssuePerformance>();

            Parallel.ForEach(companyProfiles,
                 new ParallelOptions
                 {
                     MaxDegreeOfParallelism = Convert.ToInt32(Math.Ceiling(Environment.ProcessorCount * 0.75))
                 },
                 companyProfile =>
                 {
                     foreach (var mei in companyProfile.MaterialEsgIssues)
                     {
                         if (!averages.TryGetValue(Tuple.Create(companyProfile.SubPeerGroupId, mei.ExternalId), out RiskRatingMeiSubPeerGroupAverage meiAverage))
                             meiAverage = new RiskRatingMeiSubPeerGroupAverage();

                         var materialIssue = new MaterialIssuePerformance
                         {
                             CompanyId = companyProfile.Id,
                             Name = mei.Name,
                             Type = (int)mei.Type,
                             SortKey = mei.SortKey.Value,
                             ExternalId = mei.ExternalId,

                             EventIndicators = mei.Indicators
                                                  .Where(i => i.IndicatorType == IndicatorType.EventIndicator && i.AnswerCategory.Value >= 3)
                                                  .Select(ei => new EventIndicatorPerformance
                                                  {
                                                      Name = ei.Name,
                                                      AnswerCategory = ei.AnswerCategory.Value
                                                  })
                                                  .ToList(),

                             UnmanagedRisk = mei.ManagementRisk.UnmanagedRisk.Value,
                             AvgUnmanagedRisk = meiAverage.UnmanagedRiskAverage,
                             Assessment = mei.Cluster.Assessment,
                           
                             Exposure = mei.ManagementRisk.Exposure.Value,
                             ExcessExposure= mei.ExcessExposure.Value,
                             AvgExposure = meiAverage.ExposureAverage,

                             SubIndustryExposure=mei.SubIndustryExposureScore.Value,
                             ManageableRiskFactorPercentile =mei.ManageableRiskFactorPercentile.Value,

                             ManagedRisk = mei.ManagementRisk.ManagedRisk.Value,
                             AvgManagedRisk = meiAverage.ManagementRiskAverage,

                             Management = mei.ManagementRisk.Management.Value,

                             FinalWeight = mei.FinalWeight.Value
                         };
                         materialIssues.Add(materialIssue);
                     }
                 });

            return materialIssues.GroupBy(mei => mei.CompanyId).ToDictionary(kv => kv.Key, v => v.OrderBy(mei => mei.Type).ThenBy(mei => mei.SortKey).ToList());
        }

        public byte[] GetGlossaryPdf(bool isSummary, bool isCore)
        {
            var glossaryName = string.Empty;
            byte[] glossaryBody = null;
            if (isCore)
                glossaryName = isSummary ? "GlossarySummaryCoreRR.pdf" : "GlossaryFullCoreRR.pdf";
            else
                glossaryName = isSummary ? "GlossarySummaryRR.pdf" : "GlossaryFullRR.pdf";
            glossaryBody = ReportHelper.GetFile(glossaryName);
            if(glossaryBody == null)
                glossaryBody = ReportHelper.GetFile("Glossary Full PDF RR.pdf");
            return glossaryBody;
        }
    }
}